package com.aboo.word2excel;

import com.aboo.word2excel.attr.CellAttribute;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.function.Predicate;
import java.util.stream.Collectors;

/**
 * @author louan
 * @date 2025-01-07
 * @description
 */
public class Word2ExcelTest04 {

    @Test
    public void test() throws IOException {
        String wordFilePath = "D:/workspace_data/JavaStudy20250106/24ZAS02P03D33-35532.docx";
        String excelFilePath = "D:/workspace_data/JavaStudy20250106/24ZAS02P03D33-35532.xlsx";

        // 指定符合预期数据的条件（红色文本的单元格）
        Predicate<XWPFTableCell> predicate = cell -> {
            for (XWPFParagraph paragraph : cell.getParagraphs()) {
                for (XWPFRun run : paragraph.getRuns()) {
                    if (run.getColor() != null && run.getColor().equals("FF0000")) {
                        return true;
                    }
                }
            }
            return false;
        };

        // 获取单元格属性
        List<CellAttribute> cellAttributes = getCellAttributes(wordFilePath, predicate);
        // 将数据写入Excel
        writeDataToExcel(excelFilePath, cellAttributes);
        // 设置单元格宽度和高度
        setCellWidthHeight(excelFilePath, cellAttributes);
        // 设置Excel区域边框
        setExcelRegionBorder(excelFilePath, cellAttributes);
    }

    /**
     * @param wordFilePath
     * @param predicate
     * @return
     * @throws IOException
     * @description 获取单元格属性
     */
    private static List<CellAttribute> getCellAttributes(String wordFilePath, Predicate<XWPFTableCell> predicate) throws IOException {
        List<CellAttribute> cellAttributes = new ArrayList<>(128);

        try (FileInputStream fis = new FileInputStream(wordFilePath);
             XWPFDocument document = new XWPFDocument(fis)) {

            for (XWPFTable table : document.getTables()) {
                for (int rowIndex = 0; rowIndex < table.getRows().size(); rowIndex++) {
                    XWPFTableRow row = table.getRow(rowIndex);

                    // 只要当前行的包含一个有效单元格，当前行就算合法
                    boolean isValidRow = false;
                    for (int colIndex = 0; colIndex < row.getTableCells().size(); colIndex++) {
                        XWPFTableCell cell = row.getCell(colIndex);
                        if (predicate.test(cell)) {
                            isValidRow = true;
                            break;
                        }
                    }
                    if (!isValidRow) {
                        continue;
                    }

                    int gap = 0;
                    for (int colIndex = 0; colIndex < row.getTableCells().size(); colIndex++) {
                        XWPFTableCell cell = row.getCell(colIndex);
                        int rowSpan = getRowSpan(cell);
                        int colSpan = getColSpan(cell);
                        // 宽度的单位是dxa（twentieths of a point）。1 dxa = 1 twip
                        int cellWidth = cell.getWidth();
                        // 高度的单位是twips（twentieths of a point）。1 point = 20 twips
                        int cellHeight = row.getHeight();
                        String cellText = cell.getText();

                        String fontFamily = null;
                        int fontSize = 0;
                        String vertAlignName = null;
                        String horizAlignName = null;
                        // 获取垂直对齐方式
                        XWPFTableCell.XWPFVertAlign vertAlign = cell.getVerticalAlignment();
                        for (XWPFParagraph paragraph : cell.getParagraphs()) {
                            ParagraphAlignment horizAlign = paragraph.getAlignment();
                            vertAlignName = vertAlign == null ? "CENTER" : vertAlign.name();
                            horizAlignName = horizAlign == null ? "CENTER" : horizAlign.name();
                            for (XWPFRun run : paragraph.getRuns()) {
                                fontFamily = run.getFontFamily();
                                fontSize = run.getFontSize();
                                break;
                            }
                            break;
                        }

                        // 本身这个单元格
                        CellAttribute cellAttribute = CellAttribute.of(rowIndex, colIndex + gap, rowSpan, colSpan, cellWidth, cellHeight, cell.getText());
                        cellAttributes.add(cellAttribute);
                        cellAttribute.setFontFamily(fontFamily);
                        cellAttribute.setFontSize(fontSize);
                        cellAttribute.setVertAlignName(vertAlignName);
                        cellAttribute.setHorizAlignName(horizAlignName);

                        // 被跨列的单元格，进行恢复
                        for (int i = 1; i < colSpan; i++) {
                            gap++;
                            CellAttribute crossCellAttribute = CellAttribute.of(rowIndex, colIndex + gap, 1, 1, 0, cellHeight, "");
                            cellAttributes.add(crossCellAttribute);
                        }
                    }
                }
            }
        }

        correctCellAttributes(cellAttributes);
        return cellAttributes;
    }

    /**
     * @param excelFilePath
     * @param cellAttributes
     * @description 将数据写入Excel
     */
    private static void writeDataToExcel(String excelFilePath, List<CellAttribute> cellAttributes) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Red Text Tables");
            for (CellAttribute cellAttribute : cellAttributes) {
                Row excelRow = sheet.getRow(cellAttribute.getRowNo());
                if (excelRow == null) {
                    excelRow = sheet.createRow(cellAttribute.getRowNo());
                }
                // 自动调整行高度
                //excelRow.setHeight((short) -1);
                Cell excelCell = excelRow.createCell(cellAttribute.getColNo());
                excelCell.setCellValue(cellAttribute.getText());

                if (cellAttribute.getFontSize() > 0) {
                    // 创建字体对象
                    Font font = workbook.createFont();
                    font.setFontHeightInPoints((short) cellAttribute.getFontSize()); // 设置字体大小
                    if (cellAttribute.getFontFamily() != null) {
                        font.setFontName(cellAttribute.getFontFamily()); // 设置字体类型
                    }

                    // 创建单元格样式对象
                    CellStyle cellStyle = workbook.createCellStyle();
                    // 将字体应用到单元格样式
                    cellStyle.setFont(font);
                    // 设置自动换行
                    cellStyle.setWrapText(true);
                    // 设置水平对齐方式为居左
                    cellStyle.setAlignment(HorizontalAlignment.valueOf(cellAttribute.getHorizAlignName()));
                    // 设置垂直对齐方式为向上对齐
                    cellStyle.setVerticalAlignment(VerticalAlignment.valueOf(cellAttribute.getVertAlignName()));

                    // 将单元格样式应用到单元格
                    excelCell.setCellStyle(cellStyle);
                }

                // 处理合并单元格
                boolean isMerged = isCellMerged(sheet, cellAttribute.getRowNo(), cellAttribute.getColNo());
                if (!isMerged && (cellAttribute.getRowSpan() > 1 || cellAttribute.getColSpan() > 1)) {
                    System.out.println(cellAttribute);
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(cellAttribute.getRowNo(), cellAttribute.getRowNo() + cellAttribute.getRowSpan() - 1, cellAttribute.getColNo(), cellAttribute.getColNo() + cellAttribute.getColSpan() - 1);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param cellAttributes
     * @description 修正Excel单元格宽度
     */
    private static Map<Integer, Integer> correctExcelCellWidth(List<CellAttribute> cellAttributes) {
        Map<Integer, Integer> cellWidthMap = cellAttributes.stream().collect(Collectors.toMap(CellAttribute::getColNo, (cell) -> {
            if (cell.getColSpan() == 1 && cell.getWidth() > 0) {
                return cell.getWidth();
            }
            return 0;
        }, (older, newer) -> Math.max(older, newer)));

        List<CellAttribute> crossCellAttributes = cellAttributes.stream().filter(cell -> cell.getColSpan() > 1)
                .sorted(Comparator.comparing(CellAttribute::getColSpan)).collect(Collectors.toList());
        for (CellAttribute cell : crossCellAttributes) {
            Integer lastWidth = cell.getWidth();
            List<Integer> lastColNos = new ArrayList<>();
            for (int i = cell.getColNo(); i < cell.getColNo() + cell.getColSpan(); i++) {
                if (cellWidthMap.get(i) == 0) {
                    lastColNos.add(i);
                } else {
                    lastWidth -= cellWidthMap.get(i);
                }
            }

            if (lastColNos.isEmpty()) {
                continue;
            }
            Integer pinWidth = lastWidth / lastColNos.size();
            lastColNos.forEach(colNo -> cellWidthMap.put(colNo, pinWidth));
        }

        return cellWidthMap;
    }

    /**
     * @param cellAttributes
     * @description 修正Excel单元格高度
     */
    private static Map<Integer, Integer> correctExcelCellHeight(List<CellAttribute> cellAttributes) {
        Map<Integer, Integer> cellHeightMap = cellAttributes.stream().collect(Collectors.toMap(CellAttribute::getRowNo, (cell) -> {
            if (cell.getRowSpan() == 1 && cell.getHeight() > 0) {
                return cell.getHeight();
            }
            return 0;
        }, (older, newer) -> Math.max(older, newer)));

        List<CellAttribute> crossCellAttributes = cellAttributes.stream().filter(cell -> cell.getRowSpan() > 1)
                .sorted(Comparator.comparing(CellAttribute::getRowSpan)).collect(Collectors.toList());
        for (CellAttribute cell : crossCellAttributes) {
            Integer lastHeight = cell.getHeight();
            List<Integer> lastRowNos = new ArrayList<>();
            for (int i = cell.getRowNo(); i < cell.getRowNo() + cell.getRowSpan(); i++) {
                if (cellHeightMap.get(i) == 0) {
                    lastRowNos.add(i);
                } else {
                    lastHeight -= cellHeightMap.get(i);
                }
            }

            if (lastRowNos.isEmpty()) {
                continue;
            }
            Integer pinWidth = lastHeight / lastRowNos.size();
            lastRowNos.forEach(colNo -> cellHeightMap.put(colNo, pinWidth));
        }

        return cellHeightMap;
    }

    /**
     * @param excelFilePath
     * @param cellAttributes
     * @throws IOException
     * @description 设置单元格宽度和高度
     */
    private static void setCellWidthHeight(String excelFilePath, List<CellAttribute> cellAttributes) throws IOException {
        Map<Integer, Integer> colWidthMap = correctExcelCellWidth(cellAttributes);
        Map<Integer, Integer> colHeightMap = correctExcelCellHeight(cellAttributes);

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet
            Row row = sheet.getRow(0);
            // 设置每列的宽度
            for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex++) {
                Integer width = colWidthMap.get(colIndex);
                if (width != null) {
                    // 宽度的数据单位是字符宽度的1/256。也就是说，传递给setColumnWidth方法的值是以1/256个字符宽度为单位的
                    sheet.setColumnWidth(colIndex, convertWordWidthToExcelWidth(width));
                }
            }
            // 设置每行高度
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Integer height = colHeightMap.get(rowIndex);
                if (height != null) {
                    Row excelRow = sheet.getRow(rowIndex);
                    excelRow.setHeightInPoints(convertWordHeightToExcelWidth(height));
                }
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param cellAttributes
     * @description 修正单元格属性
     */
    private static void correctCellAttributes(List<CellAttribute> cellAttributes) {
        int minRowNo = cellAttributes.stream().mapToInt(CellAttribute::getRowNo).min().orElse(0);
        int minColNo = cellAttributes.stream().mapToInt(CellAttribute::getColNo).min().orElse(0);
        if (minRowNo != 0 || minColNo != 0) {
            for (CellAttribute cell : cellAttributes) {
                cell.setRowNo(cell.getRowNo() - minRowNo);
                cell.setColNo(cell.getColNo() - minColNo);
            }
        }
    }

    /**
     * @param sheet
     * @param rowIndex
     * @param colIndex
     * @return
     * @description 判断单元格是否已经合并
     */
    private static boolean isCellMerged(Sheet sheet, int rowIndex, int colIndex) {
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress region : mergedRegions) {
            if (region.isInRange(rowIndex, colIndex)) {
                return true;
            }
        }
        return false;
    }

    /**
     * @param excelFilePath
     * @param cellAttributes
     * @throws IOException
     * @description 设置Excel区域边框
     */
    private static void setExcelRegionBorder(String excelFilePath, List<CellAttribute> cellAttributes) throws IOException {
        int firstRow = cellAttributes.stream().mapToInt(CellAttribute::getRowNo).min().orElse(0);
        int lastRow = cellAttributes.stream().mapToInt(cell -> cell.getRowNo() + cell.getRowSpan() - 1).max().orElse(0);
        int firstCol = cellAttributes.stream().mapToInt(CellAttribute::getColNo).min().orElse(0);
        int lastCol = cellAttributes.stream().mapToInt(cell -> cell.getColNo() + cell.getColSpan() - 1).max().orElse(0);

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            // 获取第一个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            // 指定区域
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);

            setRegionBorder(sheet, region, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    private static void setRegionBorder(Sheet sheet, CellRangeAddress region, BorderStyle borderStyle, short color) {
        for (int rowIndex = region.getFirstRow(); rowIndex <= region.getLastRow(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            for (int colIndex = region.getFirstColumn(); colIndex <= region.getLastColumn(); colIndex++) {
                Cell cell = row.getCell(colIndex);
                if (cell == null) {
                    cell = row.createCell(colIndex);
                }
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                cellStyle.cloneStyleFrom(cell.getCellStyle());
                cellStyle.setBorderTop(borderStyle);
                cellStyle.setBorderBottom(borderStyle);
                cellStyle.setBorderLeft(borderStyle);
                cellStyle.setBorderRight(borderStyle);
                cellStyle.setTopBorderColor(color);
                cellStyle.setBottomBorderColor(color);
                cellStyle.setLeftBorderColor(color);
                cellStyle.setRightBorderColor(color);
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private static int getRowSpan(XWPFTableCell cell) {
        if (cell.getCTTc().isSetTcPr() && cell.getCTTc().getTcPr().isSetVMerge()) {
            return cell.getCTTc().getTcPr().getVMerge().getVal().intValue();
        }
        return 1; // Default row span is 1
    }

    private static int getColSpan(XWPFTableCell cell) {
        if (cell.getCTTc().isSetTcPr() && cell.getCTTc().getTcPr().isSetGridSpan()) {
            return cell.getCTTc().getTcPr().getGridSpan().getVal().intValue();
        }
        return 1; // Default column span is 1
    }

    /**
     * @param wordWidth
     * @return
     * @description 将Word中的宽度转换为Excel中的宽度
     */
    private static int convertWordWidthToExcelWidth(int wordWidth) {
//        return (wordWidth * 256) / 1440;
        return (int)(wordWidth * 2.5);
    }

    /**
     * @param wordHeight
     * @return
     * @description 将Word中的高度转换为Excel中的高度
     */
    private static int convertWordHeightToExcelWidth(int wordHeight) {
//        return (wordWidth * 256) / 1440;
        return (int)(wordHeight * 0.05);
    }
}
